package ch.musicorg.logic;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import ch.musicorg.utilities.SQLVerbindung;

/**
 * 
 * @author Florian
 * @since 20.01.2012
 * @version 1.0
 * In dieser Klasse werden alle Abfragen an die Datenbank gemacht
 * 
 */
public class SQLAbfrage {
	//Initialisieren der Verbindung
	private SQLVerbindung verbindung;
	private Connection connection = null;
	//Initialisieren der ArrayList
	private ArrayList<String> ALInhalt;
	
	//PREPARE STATEMENTS
	final private String GET_STIL_ID = "SELECT stilID FROM tbl_stil WHERE Stil=?";
	final private String GET_ALBUM_ID = "SELECT AlbumID FROM tbl_album WHERE tbl_album.InterpretID = ? AND tbl_album.Name = ?";
	final private String GET_INTERPRET_ID = "SELECT interpretID FROM tbl_interpret WHERE tbl_interpret.Name = ?";
	final private String GET_ALLE_TITEL = "SELECT tbl_titel.Name FROM tbl_titel WHERE tbl_titel.AlbumID = ?";
	final private String GET_ALLE_ALBEN = "SELECT tbl_album.AlbumID FROM tbl_album WHERE tbl_album.InterpretID = ?";
	
	//Verbindung mit DB wird erstellt
	public SQLAbfrage(){
		verbindung = new SQLVerbindung();
		connection = verbindung.getDBVerbindung();
	}
	
	//Stil wird ausgelesen
	public ResultSet getStil() throws SQLException{
		ResultSet resultSet = null;
		Statement statement = connection.createStatement();
		try {
			resultSet = statement.executeQuery("SELECT stil FROM tbl_stil");
		} catch (SQLException e) {
			e.getMessage();
		}
		return resultSet;
	}
	
	//StilID wird ausgelsen
	public int getStilID(String stilIn) throws SQLException{
		int stilID = -1;
		ResultSet stilIDSet = null;
		try{
			PreparedStatement pstatement = connection.prepareStatement(GET_STIL_ID);
			pstatement.setString(1, stilIn);
			stilIDSet = pstatement.executeQuery();
			while(stilIDSet.next()){
				stilID = Integer.parseInt(stilIDSet.getString(1));
			}
			return stilID;
			
		} catch (SQLException e){
			e.getMessage();
		}
		return stilID;
	}
	
	//Die Anzahl Titel wird aus der DB geholt
	public int getAnzTitel() throws SQLException{
		ResultSet titel = null;
		Statement titelStatement = connection.createStatement();
		titel = titelStatement.executeQuery("SELECT COUNT(TitelID) FROM tbl_Titel");
		while(titel.next()){
			return titel.getInt(1);
		}
		return -1;
	}
	
	//Die Anzahl Alben wird aus der DB geholt
	public int getAnzAlbum() throws SQLException{
		ResultSet album = null;
		Statement albumStatement = connection.createStatement();
		album = albumStatement.executeQuery("SELECT COUNT(AlbumID) FROM tbl_Album");
		while(album.next()){
			return album.getInt(1);
		}
		return -1;
	}
	
	//Die Anzahl Interpreten wird aus der DB geholt
	public int getAnzInterpret() throws SQLException{
		ResultSet interpret = null;
		Statement interpretStatement = connection.createStatement();
		interpret = interpretStatement.executeQuery("SELECT COUNT(DISTINCT Name) FROM tbl_Interpret");
		while(interpret.next()){
			return interpret.getInt(1);
		}
		return -1;
	}
	
	//Tabelleninhalt des GUIListe wird ausgelesen
	public ResultSet getTabellenInhalt() throws SQLException{
		ResultSet tblInhalt = null;
		Statement tblInhaltStatement = connection.createStatement();
		tblInhalt = tblInhaltStatement.executeQuery("SELECT tbl_interpret.name, tbl_album.name, tbl_Album.jahrgang, tbl_stil.Stil, tbl_titel.Name, tbl_interpret.Link " +
				"FROM tbl_interpret, tbl_album, tbl_stil, tbl_titel WHERE tbl_interpret.InterpretID = tbl_album.InterpretID " +
				"AND tbl_stil.StilID = tbl_album.StilID AND tbl_album.AlbumID = tbl_titel.AlbumID");
		return tblInhalt;
		
	}
	
	//Die InterpretID wird ausgelsen
	public int getInterpretID(String interpretName) throws SQLException{
		int interpretID = -1;
		ResultSet interpret = null;
		PreparedStatement pstatement = connection.prepareStatement(GET_INTERPRET_ID);
		pstatement.setString(1, interpretName);
		interpret = pstatement.executeQuery();
		while(interpret.next()){
			interpretID = interpret.getInt(1);
			return interpretID;
		}
		return -1;
	}
	
	//Die AlbumID wird ausgelsen
	public int getAlbumID(int interpretID, String albumName) throws SQLException{
		int albumID = -1;
		ResultSet album = null;
		PreparedStatement pstatement = connection.prepareStatement(GET_ALBUM_ID);
		pstatement.setInt(1, interpretID);
		pstatement.setString(2, albumName);
		album = pstatement.executeQuery();
		while(album.next()){
			albumID = album.getInt(1);
			return albumID;
		}
		return -1;
	}
	
	//Alle Titel zu einem bestimmten Album werden ausgelesen
	public ResultSet getAlleTitel(int albumIDIn) throws SQLException{
		ResultSet result = null;
		PreparedStatement pstatement = connection.prepareStatement(GET_ALLE_TITEL);
		pstatement.setInt(1, albumIDIn);
		result = pstatement.executeQuery();
		return result;
	}
	
	//Alle Alben zu einem bestimmten Interpreten werden ausgelesen
	public ResultSet getAlleAlbenIDs(int interpretIDIn) throws SQLException{
		ResultSet result = null;
		PreparedStatement pstatement = connection.prepareStatement(GET_ALLE_ALBEN);
		pstatement.setInt(1, interpretIDIn);
		result = pstatement.executeQuery();
		return result;	
		
	}
}